/**
 * 根据指定的卡片模型获取数据
 * 
 * Created by wbx on 2022/05/01
 * 
 * 输入参数:
 *   Card:       必需,     字符串,        卡片名称
 *   Model:                数组,          字段名的数组. 可包含数据或索引的名称
 *   Filter:               数组,         过滤条件. 格式为: [ [三元素数组], { and/or: [] } ]
 *   Order:                键值对象,      排序列. 键:字符串,指定别名; 值:字符串,指定升序(asc)或降序(desc), 默认按记录时间倒序排列, 不存在记录时间时用ID排序
 *   PageSize:             数值           页面大小
 *   PageNo:               数值           页码
 * 输出参数:
 *   键值对象, JS数据和页码信息
 * 
 * 示例:
 * A.job.CardR({
 *    Card: 'B_员工字典',
 *    Model: ['姓名', '隶属部门.名称'],
 *    Filter: [ ['隶属部门.名称', '=', '办公室'], { or: [ ['姓名', '=', '张三'], ['姓名', '=', '李四'] ] } ]
 * });
 * 
 */
(() => {
  function parse_filter(filter, fields, provider) {
    const roots = [];
    Object.keys(filter).forEach(key => {
      switch (key) {
        case 'and':
        case 'or': {
          const items = filter[key];
          let t = [];
          let v = [];
          items.forEach(item => {
            if (!item) return;
            if (Array.isArray(item)) {
              let d = parse_triple(item, fields, provider);
              t.push(d.t);
              v = v.concat(d.v);
            } else if (Object.getPrototypeOf(item) === Object.prototype) {
              let d = parse_filter(item, fields, provider);
              t.push(`(${d.t})`);
              v = v.concat(d.v);
            } else {
              throw Error("Filter格式错误");
            }
          });
          roots.push({
            t: t.join(` ${key.toUpperCase()} `), v
          });
          break;
        }
      }
    });
    if (roots.length > 1) {
      let t = [];
      let v = [];
      roots.forEach(d => {
        t.push(`(${d.t})`);
        v = v.concat(d.v);
      });
      return {
        t: t.join(' AND '),
        v
      };
    } else {
      return roots[0] ?? {
        t: null,
        v: []
      };
    }
  }

  function parse_triple([source, operator, value], fields, provider) {
    const field = fields.get(source);
    if (!field) throw Error(`${source}的模型信息缺失`);
    switch (operator.toUpperCase()) {
      case '=':
      case '>':
      case '<':
      case '>=':
      case '<=':
      case '<>': {
        return {
          t: `${field.column_alias} ${operator}?`,
          v: [field.column_type === 'BJ' ? (value ? 1 : 0) : value]
        };
      }
      case 'IS': {
        return {
          t: `${field.column_alias} IS ${value == null ? '' : 'NOT '}NULL`,
          v: []
        };
      }
      case 'IS NULL': { // 废弃
        return {
          t: `${field.column_alias} IS ${(value ?? true) ? '' : 'NOT '}NULL`,
          v: []
        };
      }
      case 'IN': {
        if (value.length === 0) {
          return { t: "1=0", v: [] };
        } else {
          return {
            t: `${field.column_alias} IN (${value.map(() => '?').join(',')})`,
            v: value
          };
        }
      }
      case 'NOT IN': {
        if (value.length === 0) {
          return { t: "1=1", v: [] };
        } else {
          return {
            t: `${field.column_alias} NOT IN (${value.map(() => '?').join(',')})`,
            v: value
          };
        }
      }
      case 'LIKE': {
        if (value) {
          return { t: `INSTR(${field.column_alias}, ?)>0`, v: [value] };
        } else {
          return { t: "1=1", v: [] };
        }
      }
      case 'NOT LIKE':
        if (value) {
          return { t: `INSTR(${field.column_alias}, ?)=0`, v: [value] };
        } else {
          return { t: "1=1", v: [] };
        }
      case 'BETWEEN':
        return {
          t: `${field.column_alias} BETWEEN ? AND ?`,
          v: [value[0], value[1]]
        };
      case 'NOT BETWEEN':
        return {
          t: `${field.column_alias} NOT BETWEEN ? AND ?`,
          v: [value[0], value[1]]
        };
      default:
        throw Error('不支持的操作符');
    }
  }

  function CardR({ UID, Card, Model = null, Filter = [], Order = {}, PageSize = 0, PageNo = 1 }) {
    // 验证用户
    if (!UID) throw Error("未授权的访问");
    if (!Array.isArray(Filter)) Filter = [Filter]; // 兼容处理

    const v_card = A.card[Card];
    const conn = A.db.connectionSettings[v_card.连接名称];
    const q = conn.provider === A.db.provider.MYSQL ? '`' : '"';

    // 预处理model参数
    if (Model == null) {
      Model = ['ID'].concat(v_card.数据.map(d => d.名称), v_card.引用.map(r => r.名称));
    }
    if (!Model.includes('ID')) Model.unshift('ID');

    // 卡片有删除标记时，自动过滤掉假删的数据
    if (v_card.数据.删除标记) {
      if (!Model.includes('删除标记')) Model.push('删除标记');
      if (!Filter.find(f => f[0] === '删除标记')) Filter.push(['删除标记', '=', 0]);
    }

    // 解析model
    const select = [];
    const joins = [{
      alias: 'T0',
      card: v_card,
      reference: null,
    }];
    const source_map = new Map();
    for (let i = 0; i < Model.length; i++) {
      const source = Model[i];
      const source_words = source.split(".");
      let join_cursor = joins[0];
      for (let j = 0; j < source_words.length; j++) {
        const word = source_words[j];
        if (word === 'ID') {
          select.push(`${join_cursor.alias}.ID C${i}`);
          source_map.set(source, {
            column_alias: `${join_cursor.alias}.ID`,
            column_type: 'TEXT',
          });
          continue;
        }
        if (join_cursor.card.数据.hasOwnProperty(word)) {
          const name = `${join_cursor.alias}.${q}${join_cursor.card.数据[word].标识符}${q}`;
          select.push(`${name} C${i}`);
          source_map.set(source, {
            column_alias: name,
            column_type: join_cursor.card.数据[word].类型,
          });
          continue;
        }
        if (join_cursor.card.引用.hasOwnProperty(word)) {
          const name = `${join_cursor.alias}.${q}${join_cursor.card.引用[word].标识符}${q}`;
          if (j === source_words.length - 1) {
            // 如果是指向引用本身,则返回引用字段
            select.push(`${name} C${i}`);
            source_map.set(source, {
              column_alias: name,
              column_type: 'TEXT',
            });
          } else {
            const target_card = A.card[join_cursor.card.引用[word].目标名称];
            if (target_card.连接名称 !== v_card.连接名称) throw Error(`CardR不支持引用的卡片使用其他连接"${word}"`);
            let join = joins.find(join => join.reference === name);
            if (!join) {
              join = {
                alias: `T${joins.length}`,
                card: target_card,
                reference: name,
              };
              joins.push(join);
            }
            join_cursor = join;
          }
          continue;
        }
        throw Error(`Failed to resolve ${source}`);
      }
    }
    // 构造左连接
    let from_sql = "";
    joins.forEach((join, index) => {
      if (index === 0)
        from_sql += `${q}${join.card.标识符}${q} ${join.alias}`;
      else
        from_sql += ` LEFT JOIN ${q}${join.card.标识符}${q} ${join.alias} ON ${join.reference}=${join.alias}.ID`;
    });
    // 构造筛选条件
    let where = "";
    let values = [];
    let { t, v } = parse_filter({ and: Filter }, source_map, conn.provider);
    if (t) {
      where = ' WHERE ' + t;
      values = v;
    }

    // 排序处理
    const order_sql = " ORDER BY " + [
      ...Object.keys(Order).map(function (source) {
        const alias = source_map.get(source);
        if (!alias) throw Error("排序条件解析失败,字段必须是模型中存在的字段");
        switch (String(Order[source]).toLowerCase()) {
          case "asc":         // 数据库默认升序
          case "ascend":
            return alias.column_alias + " ASC";
          case "null-asc":    // 空值最小的升序
            return "CASE WHEN " + alias.column_alias + " IS NULL THEN 0 ELSE 1 END ASC," + alias.column_alias + " ASC";
          case "asc-null":    // 空值最大的升序
            return "CASE WHEN " + alias.column_alias + " IS NULL THEN 0 ELSE 1 END DESC," + alias.column_alias + " ASC";
          case "desc":        // 数据库默认降序
          case "descend":
            return alias.column_alias + " DESC";
          case "null-desc":   // 空值最大的降序
            return "CASE WHEN " + alias.column_alias + " IS NULL THEN 0 ELSE 1 END ASC," + alias.column_alias + " DESC";
          case "desc-null":   // 空值最小的降序
            return "CASE WHEN " + alias.column_alias + " IS NULL THEN 0 ELSE 1 END DESC," + alias.column_alias + " DESC";
          default:
            throw Error("未能识别排序方式");
        }
      }),
      // 强制增加ID列作为排序条件, 避免Oracle分页的记录重复问题
      "T0.ID"
    ].join(',');

    // 构造查询SQL
    const sql = `SELECT ${select.join(',')} FROM ${from_sql}` + where + order_sql;

    // 执行分页查询
    const result = A.job.Paginate({
      Connection: v_card.连接名称,
      Text: sql,
      Params: values,
      PageSize,
      PageNo,
      UID,
    });

    // 模型映射
    result.Rows.forEach(row => {
      Model.forEach((source, index) => {
        row[source] = row[`C${index}`];
        delete row[`C${index}`];
      })
    });

    return result;
  }

  return (arg1, arg2, arg3, arg4) => {
    if (typeof arg1 === 'string' && typeof arg2 === 'string') {
      let other = {};
      if (typeof arg4 === 'string') {
        other.Filter = [["ID", '=', arg4]];
      } else if (Array.isArray(arg4)) {
        other.Filter = arg4;
      } else {
        other = arg4;
      }
      return CardR({
        UID: arg1,
        Card: arg2,
        Model: arg3,
        ...other
      });
    } else {
      // 检查卡片访问控制表
      if (arg2 && A.setting('EnableCardACL')) {
        const acl = A.cache.get(`CardACL.${arg1.Card}`, () => {
          return A.db.execute('KERNEL', "SELECT * FROM KPFWKZ WHERE KPMC=?", [arg1.Card]).rows;
        }, 60); // 缓存60秒
        if (!acl.some(item => !!item.KFKD) && !A.job.JobEvent({
          Event: 'OnCheckPageACL',
          UID: arg1.UID,
          PageIds: acl.filter(item => item.GLYM_ID).map(item => item.GLYM_ID),
        }, arg2)) {
          throw Error("You are not authorized to access this card");
        }
      }
      return CardR(arg1);
    }
  };

})();